<?php

class HomeController extends BaseController {
    /*
      |--------------------------------------------------------------------------
      | Default Home Controller
      |--------------------------------------------------------------------------
      |
      | You may wish to use controllers instead of, or in addition to, Closure
      | based routes. That's great! Here is an example controller method to
      | get you started. To route to this controller, just add the route:
      |
      |	Route::get('/', 'HomeController@showWelcome');
      |
     */

    public function getTitle(){

         $titles = array(
                        "客戶姓名", "特徵", "重要性", "國籍", 
                        "英文姓名", "性別", "客戶來源", 
                        "生日", "送禮", "身份證字號", 
                        "平常聯絡", "開始時間", "結束時間", 
                        "平常聯絡1", "開始時間1", "結束時間1", 
                        "平常聯絡2", "開始時間2", "結束時間2", 
                        "假日聯絡", "結束時間", "開始時間", 
                        "假日聯絡1", "開始時間1", "結束時間1", 
                        "假日聯絡2", "開始時間2", "結束時間2", 

                        "背景與特色",

                        "行動電話", "簡訊發送", 
                        "行動電話1", "簡訊發送1", 
                        "行動電話2", "簡訊發送2", 
                        "住家電話", "住家電話1", 
                        "住家電話2", 
                        "公司電話", 
                        "公司電話1", "公司電話2", 
                        "分機", "分機1", "分機2", 
                        "電子郵件", "寄發EDM", 
                        "電子郵件1", "寄發EDM1", 
                        "電子郵件2", "寄發EDM2", 

                        "傳真", "傳真1", "傳真2", 

                        "住家地址", "大樓名稱", "住家寄發刊物", 
                        "住家地址1", "大樓名稱1", "住家寄發刊物1", 
                        "住家地址2", "大樓名稱2", "住家寄發刊物2", 

                        "公司名稱", "公司類型", "統一編號", "部門", 
                        "職稱", "類別", 

                        "公司地址", "大樓名稱", 
                        "公司寄發刊物", "公司網址",

                         "公司名稱1", "公司類型1", "統一編號1", 
                         "部門1", "職稱1", "類別1", 
                         "公司地址1", "大樓名稱1", "公司寄發刊物1", 
                        "公司網址1", 

                        "公司名稱2", "公司類型2", "統一編號2", 
                        "部門2", "職稱2", "類別2", 
                        "公司地址2", "大樓名稱2", 
                        "公司寄發刊物2", "公司網址2", 

                        "接待人", "主管", 
                         "經紀人");
        return $titles;
    }  

     

    public function clientSelectField(){

      return Client::select( 
                              "name" , "recommended" ,  
                              "importance" , "nationality" ,

                              "english_name" ,"sex" , "customer_source" ,
                              "birthday" , "gifts" , "id_number" , 
                              "usually_contact" , "usually_start_time" , "usually_end_time" , 
                              "usually_contact1" , "usually_start_time1" , "usually_end_time1" , 
                              "usually_contact2" , "usually_start_time2" , "usually_end_time2" ,

                              "holidays_contact" , "holidays_start_time" , "holidays_end_time" , 
                              "holidays_contact1" , "holidays_start_time1" , "holidays_end_time1" , 
                              "holidays_contact2" , "holidays_start_time2" , "holidays_end_time2" , 
                              "background_and_features" ,


                              "mobile" ,"send_sms" ,
                              "mobile1" ,"send_sms1" ,
                              "mobile2" , "send_sms2" , 
                              "home_phone" , 
                              "home_phone1" , 
                              "home_phone2" , 
                              "company_phone" , "company_phone1" ,"company_phone2" , 
                              "company_phone_ext" ,  
                              "company_phone_ext1" , 
                              "company_phone_ext2" ,
                               "email" , "send_email" , 
                               "email1" , "send_email1" , 
                               "email2" , "send_email2" , 




                               "fax" , "fax1" , "fax2" , 

                               "home_address" , "home_building_name" , "home_send_publication" , 
                               "home_address1" , "home_building_name1" , "home_send_publication1" , 
                               "home_address2" ,"home_building_name2" , "home_send_publication2" , 

                               "company_name" , "type_of_company" , "unicode" , "department" ,
                                "title" , "category" , 

                              "company_address" , "company_building_name" , 
                              "company_send_publication" , "company_website" , 

                              "company_name1" , "type_of_company1" , "unicode1" , 
                              "department1" , "title1" , "category1" , 
                              "company_address1" , "company_building_name1" , "company_send_publication1" ,
                              "company_website1" , 

                               "company_name2" , "type_of_company2" , "unicode2" , 
                               "department2" , "title2" , 
                               "category2" , "company_address2" , "company_building_name2" , 
                               "company_send_publication2" , "company_website2" , 
                               
                               "front_man" , "head" , 
                               "broker" 
                                );

    }  

    public function showWelcome() {
        return View::make('hello');
    }

    public function getClientbyId() {

        $_cits=DB::table("h_saveclientid")->get();


        $refData = $this->refData();
        $clients =$this->clientSelectField();
        foreach($_cits as $_c){

            $clients->orwhere("client_id","=",$_c->client_id);

        }
      $clients=$clients->get()->toArray();

        $d = array();
        $titles = $this->getTitle();
        $icount = 1;
        //$d[0]=$titles; 
        
        $xx=$this->fuckExcel($clients ,$titles);
            
            //$icount++;
        //}
        //$d[0]='';  
        DB::table("h_saveclientid")->truncate();
        return $xx;
    }


    public function kk() {


        //$refData = $this->refData();
        $clients  = $this->clientSelectField();
        $clients  = $clients->get()->toArray();
        //$d = array();
        $titles = $this->getTitle();
        $icount = 1;
         
        $x=$this->fuckExcel($clients ,$titles);


         
        return $x;
    }

    public function excel() {

        
        $data = $this->kk();
        
        Excel::create(date("ymd_his"), function($excel) use($data) {

            $excel->sheet('Sheetname', function($sheet) use($data) {


                $sheet->setAutoSize(true);
                
                $sheet->setFont(array(               
                    'size'       => '14',
                    'bold'       => true
                ));


                $sheet->fromArray($data);
            });
        })->export('xls');
    }


    public function postClientId(){
      
      $_pa=Input::all();
      //print_r($_pa['id']);
      $_ids=explode(',', $_pa['id']);
      
      foreach ($_ids as $key => $id) {

           DB::table("h_saveclientid")->insert( array('client_id'=>$id) );
      
      }
      //echo $_pa["id"];
      //h_saveclientid
    }
    public function getClientExcelById(){

      //$_pa=Input::all();

      //print_r($_pa);


        $data = $this->getClientbyId();
        
        Excel::create(date("ymd_his"), function($excel) use($data) {

            $excel->sheet('Sheetname', function($sheet) use($data) {

                $sheet->setAutoSize(true);

                $sheet->setFont(array(               
                    'size'       => '14',
                    'bold'       => true
                ));


                $sheet->fromArray($data);
            });
        })->export('xls');


    }




    public function refData(){
      $data=array();

      $h_nationality=DB::table("h_nationality")->paginate(100)->toArray();
      $data["h_nationality"] =$h_nationality["data"];

      //h_contact_usual_holiday
      $h_contact_usual_holiday=DB::table("h_contact_usual_holiday")->paginate(100)->toArray();
      $data["h_contact_usual_holiday"] =$h_contact_usual_holiday["data"];


      $h_customer_source=DB::table("h_customer_source")->paginate(100)->toArray();
      $data["h_customer_source"] =$h_customer_source["data"];

      $h_title=DB::table("h_title")->paginate(100)->toArray();
      $data["h_title"] =$h_title["data"];      

      $h_type_of_company=DB::table("h_type_of_company")->paginate(100)->toArray();
      $data["h_type_of_company"] =$h_type_of_company["data"];

      $h_users=DB::table("h_users")->paginate(100)->toArray();
      $data["h_users"] =$h_users["data"];

      return $data;
      //print_r($data);

    }


    function fuckExcel($clients,$title){
      $d = array();
      $refData = $this->refData();

      $icount=1;
      $d[0]=$title;

      foreach ($clients as $aa => $bb) {

            foreach ($bb as $a => $b) {
              
                // if($b=='0' OR $b==0 OR empty($b)){
                //       $d[$icount][$a] = ' ';
                //     }else{

                if($a=="sex"){

                  $sex='';
                  if($b==0){
                    $sex='女';
                    $d[$icount][$a] = $sex;
                  }elseif($b==1){
                    $sex='男';
                    $d[$icount][$a] = $sex;
                  }else{
                    $d[$icount][$a] = '-';
                  }

                  


                }elseif($a=='holidays_contact' OR $a=='usually_contact' OR 
                        $a=='holidays_contact1' OR $a=="holidays_contact2" OR 
                        $a=='usually_contact1'OR $a=='usually_contact2'){
                  
                  foreach($refData['h_contact_usual_holiday'] as $h_contact_usual_holiday){
                    // echo $h_nationality->nationality_id;
                    // echo "/".$b.'';
                    // echo '<br>';
                    if(!empty($b)){

                      if($b==$h_contact_usual_holiday->contact_usual_holiday_id){
                        //echo $h_nationality->value;
                        $d[$icount][$a] = $h_contact_usual_holiday->value;
                      }

                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }

                }elseif($a=='nationality'){
                  
                  foreach($refData['h_nationality'] as $h_nationality){
                    // echo $h_nationality->nationality_id;
                    // echo "/".$b.'';
                    // echo '<br>';
                    if(!empty($b)){

                      if($b==$h_nationality->nationality_id){
                        //echo $h_nationality->value;
                        $d[$icount][$a] = $h_nationality->value;
                      }

                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }



                }elseif(   $a=='fax'
                        OR $a=='fax1'
                        OR $a=='fax2' 
                        OR $a=='mobile' 
                        OR $a=='company_phone' 
                        OR $a=='company_phone1' 
                        OR $a=='company_phone2'
                        OR $a=="mobile"
                        OR $a=="mobile1"
                        OR $a=="mobile2"
                        OR $a=="home_phone"
                        OR $a=="home_phone1"
                        OR $a=="home_phone2"){
                  
                  
                    if(!empty($b)){

                       
                        //echo $h_nationality->value;
                        $d[$icount][$a] = " ".$b ." ";
                       

                    }else{
                      $d[$icount][$a] = $b;
                    }
                 

                }elseif($a=='customer_source'){
                  
                  foreach($refData['h_customer_source'] as $h_customer_source){
                    if(!empty($b)){
                      if($b==$h_customer_source->customer_source_id){                      
                        $d[$icount][$a] = $h_customer_source->value;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                }elseif($a=='title' OR $a=='title1' OR $a=='title2' ){
                  
                  foreach($refData['h_title'] as $h_title){
                    if(!empty($b)){
                      if($b==$h_title->title_id){                      
                        $d[$icount][$a] = $h_title->value;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                }elseif($a=='type_of_company'){
                  
                  foreach($refData['h_type_of_company'] as $h_type_of_company){
                    if(!empty($b)){
                      if($b==$h_type_of_company->type_of_company_id){                      
                        $d[$icount][$a] = $h_type_of_company->value;
                      }
                    }else{
                      $d[$icount][$a] = $b;
                    }  
                  }
                }elseif($a=='type_of_company1'){
                  
                  foreach($refData['h_type_of_company'] as $h_type_of_company){
                    if(!empty($b)){
                      if($b==$h_type_of_company->type_of_company_id){                      
                        $d[$icount][$a] = $h_type_of_company->value;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                }elseif($a=='type_of_company2'){
                  
                  foreach($refData['h_type_of_company'] as $h_type_of_company){
                    if(!empty($b)){
                      if($b==$h_type_of_company->type_of_company_id){                      
                        $d[$icount][$a] = $h_type_of_company->value;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                }elseif($a=='broker'){
                  
                  foreach($refData['h_users'] as $h_users){
                    if(!empty($b)){
                      if($b==$h_users->users_id){                      
                        $d[$icount][$a] = $h_users->name;
                      }
                    }else{
                      $d[$icount][$a] = $b;
                    }  
                  }

                }elseif($a=='head'){
                  
                  foreach($refData['h_users'] as $h_users){
                    if(!empty($b)){
                      if($b==$h_users->users_id){                      
                        $d[$icount][$a] = $h_users->name;
                      }
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                  
                }elseif($a=='front_man'){
                  
                  foreach($refData['h_users'] as $h_users){
                    if(!empty($b)){
                      if($b==$h_users->users_id){                      
                        $d[$icount][$a] = $h_users->name;
                      }
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                  
                }elseif($a=='original_broker'){
                  
                  foreach($refData['h_users'] as $h_users){
                    if(!empty($b)){
                      if($b==$h_users->users_id){                      
                        $d[$icount][$a] = $h_users->name;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    } 
                  }
                  
                }else{

                  if(empty($b)){
                    $d[$icount][$a] = '-';
                  }else{
                    $d[$icount][$a] = $b;
                  }
                  

                }
              ////////  
              //}

            }
            
            $icount++;
        }

        return $d;

    }

}
